CREATE EXTERNAL TABLE spmi_dm.dm_transfer_income_pay_daily_summary(
  `bill_generation_date` date  comment '账单产生时间',
  `update_dt` date  comment '计算时间' ,
  `sender_manage_code` string comment '所属大区code',
  `sender_manage_name` string comment '所属大区name' ,
  `sender_province_id` bigint comment '所属省份id',
  `sender_province_name` string comment '所属省份name',
  `send_financial_center_code` string comment '所属代理区code',
  `send_financial_center_name` string comment '所属代理区name',
  `begin_code` string comment '寄件城市code',
  `begin_name` string comment '寄件城市name',
  `send_franchisee_code` string comment '寄件加盟商code',
  `send_franchisee_name` string comment '寄件加盟商name',
  `send_network_code` string comment '寄件网点code',
  `send_network_name` string comment '寄件网点name',
  `business_model_id` string comment '寄件网点经营模式id',
  `business_model_desc` string comment '寄件网点经营模式name',
  `receiver_national` string comment '目的区域',
  `receiver_province_id` bigint comment '目的省份id',
  `receiver_province_name` string comment '目的省份name',
  `destination_code` string comment '目的城市code',
  `destination_name` string comment '目的城市name',
   `sender_count` bigint comment '件量',
  `accounts_sum` decimal(21,8) comment '应收中转费总额',
  `accounts_avg` decimal(21,8) comment '单票平均中转费收入',
  `sum_weight` decimal(21,2) comment '总重量',
  `weight_avg` decimal(21,2) comment '单票平均重量',
  `cross_transfer_sum` decimal(21,8) comment '跨区中转费总额',
  `cross_count` decimal(21,8) comment '跨区中转单量',
  `cross_avg_cnt` decimal(21,8)  comment '跨区中转次数' ,
  `kg_part` int comment '公斤段'
  )
PARTITIONED BY (
  `dt` string COMMENT '账单产生时间')
STORED AS parquet
  location 'hdfs://yl-bg-hdp/dw/hive/spmi_dm.db/external/dm_transfer_income_pay_daily_summary'

ALTER TABLE spmi_dm.dm_transfer_income_pay_daily_summary ADD COLUMNS (begin_financial_center_code string COMMENT '始发代理区编码') CASCADE;
ALTER TABLE spmi_dm.dm_transfer_income_pay_daily_summary ADD COLUMNS (begin_financial_center_name string COMMENT '始发代理区') CASCADE;


--增量表
CREATE EXTERNAL TABLE spmi_dm.dm_transfer_income_pay_daily_summary_incre(
  `bill_generation_date` date  comment '账单产生时间',
  `update_dt` date  comment '计算时间' ,
  `sender_manage_code` string comment '所属大区code',
  `sender_manage_name` string comment '所属大区name' ,
  `sender_province_id` bigint comment '所属省份id',
  `sender_province_name` string comment '所属省份name',
  `send_financial_center_code` string comment '所属代理区code',
  `send_financial_center_name` string comment '所属代理区name',
  `begin_code` string comment '寄件城市code',
  `begin_name` string comment '寄件城市name',
  `send_franchisee_code` string comment '寄件加盟商code',
  `send_franchisee_name` string comment '寄件加盟商name',
  `send_network_code` string comment '寄件网点code',
  `send_network_name` string comment '寄件网点name',
  `business_model_id` string comment '寄件网点经营模式id',
  `business_model_desc` string comment '寄件网点经营模式name',
  `receiver_national` string comment '目的区域',
  `receiver_province_id` bigint comment '目的省份id',
  `receiver_province_name` string comment '目的省份name',
  `destination_code` string comment '目的城市code',
  `destination_name` string comment '目的城市name',
   `sender_count` bigint comment '件量',
  `accounts_sum` decimal(21,8) comment '应收中转费总额',
  `accounts_avg` decimal(21,8) comment '单票平均中转费收入',
  `sum_weight` decimal(21,2) comment '总重量',
  `weight_avg` decimal(21,2) comment '单票平均重量',
  `cross_transfer_sum` decimal(21,8) comment '跨区中转费总额',
  `cross_count` decimal(21,8) comment '跨区中转单量',
  `cross_avg_cnt` decimal(21,8)  comment '跨区中转次数' ,
  `kg_part` int comment '公斤段',
  `begin_financial_center_code` string comment '始发代理区编码',
  `begin_financial_center_name` string comment '始发代理区'
  )
PARTITIONED BY (
  `dt` string COMMENT '账单产生时间')
STORED AS parquet
  location 'hdfs://yl-bg-hdp/dw/hive/spmi_dm.db/external/dm_transfer_income_pay_daily_summary_incre'


--推送表
CREATE EXTERNAL TABLE spmi_tmp.dm_transfer_income_pay_daily_summary_sync(
  `bill_generation_date` string  comment '账单产生时间',
  `update_dt` string  comment '计算时间' ,
  `sender_manage_code` string comment '所属大区code',
  `sender_manage_name` string comment '所属大区name' ,
  `sender_province_id` string comment '所属省份id',
  `sender_province_name` string comment '所属省份name',
  `send_financial_center_code` string comment '所属代理区code',
  `send_financial_center_name` string comment '所属代理区name',
  `begin_code` string comment '寄件城市code',
  `begin_name` string comment '寄件城市name',
  `send_franchisee_code` string comment '寄件加盟商code',
  `send_franchisee_name` string comment '寄件加盟商name',
  `send_network_code` string comment '寄件网点code',
  `send_network_name` string comment '寄件网点name',
  `business_model_id` string comment '寄件网点经营模式id',
  `business_model_desc` string comment '寄件网点经营模式name',
  `receiver_national` string comment '目的区域',
  `receiver_province_id` string comment '目的省份id',
  `receiver_province_name` string comment '目的省份name',
  `destination_code` string comment '目的城市code',
  `destination_name` string comment '目的城市name',
   `sender_count` string comment '件量',
  `accounts_sum` string comment '应收中转费总额',
  `accounts_avg` string comment '单票平均中转费收入',
  `sum_weight` string comment '总重量',
  `weight_avg` string comment '单票平均重量',
  `cross_transfer_sum` string comment '跨区中转费总额',
  `cross_count` string comment '跨区中转单量',
  `cross_avg_cnt` string  comment '跨区中转次数' ,
  `kg_part` string comment '公斤段',
  `begin_financial_center_code` string comment '始发代理区编码',
  `begin_financial_center_name` string comment '始发代理区',
  `is_delete` int comment '是否删除'
)
STORED AS AVRO
  location 'hdfs://yl-bg-hdp/dw/hive/spmi_tmp.db/external/dm_transfer_income_pay_daily_summary_sync'